﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DB_QLSV.BusinessClassLibrary
{
    public class DiemBUS
    {
        private static QLSVDataContext db = new QLSVDataContext();
        public static bool ThemDiem(Diem Diem)
        {
            try
            {
                db.Diems.InsertOnSubmit(Diem);
                db.SubmitChanges();
            }
            catch
            {
                //conflict primary key - the item is exist
                return false;
            }
            return true;
        }
        public static bool XoaDiem(int idDiem)
        {
            try
            {
                Diem old = db.Diems.Single(kh => kh.madangkyhoc == idDiem);
                db.Diems.DeleteOnSubmit(old);
                db.SubmitChanges();
            }
            catch
            {
                // the item is not exist
                return false;
            }
            return true;
        }
        public static bool CapNhatDiemHoc(int id, Diem Diem)
        {
            try
            {
                Diem old = db.Diems.Single(kh => kh.madangkyhoc == id);              
                old.diemgiuaky = Diem.diemgiuaky;
                old.diemcuoiky = Diem.diemcuoiky;
                old.diemtrungbinh = Diem.diemtrungbinh;
                db.SubmitChanges();
            }
            catch
            {
                // the item is not exist
                return false;
            }
            return true;
        }
        public static List<Diem> LayDanhSach()
        {
            return db.Diems.ToList();
        }
        public static List<Diem> LayDanhSachTheoMaMonHoc(String mamonhoc)
        {
            var list = from kq in db.Diems
                       join dk in db.DangKyHocs on kq.madangkyhoc equals dk.madkh
                       join lmh in db.Lop_MonHocs on dk.mamonhoc equals lmh.id
                       where lmh.masomonhoc==mamonhoc
                       select kq;
            List<Diem> result = new List<Diem>();
            foreach (Diem item in list)
            {
                result.Add(item);
            }
            if (result.Count > 0)
                return result;
            return null;

        }

        public static List<BangDiem> LayBangDiemTheoLopVaMonHoc(String malop, String mamonhoc)
        {
            List<BangDiem> result = new List<BangDiem>();
            var query = from sinhvien in db.SinhViens
                    // join lop in db.Lops on sinhvien.lop equals lop.malop
                     join dangkyhoc in db.DangKyHocs on sinhvien.mssv equals dangkyhoc.mssv                     
                     join lopmonhoc in db.Lop_MonHocs on dangkyhoc.mamonhoc equals lopmonhoc.id
                     join diem in db.Diems on dangkyhoc.madkh equals diem.madangkyhoc
                     where lopmonhoc.masolop==malop
                     //where sinhvien.lop == malop
                     where lopmonhoc.masomonhoc == mamonhoc                     
                     select new { sinhvien.mssv, sinhvien.hoten, diem.diemgiuaky,diem.diemcuoiky, diem.diemtrungbinh };
            foreach (var item in query)
            {
                BangDiem temp = new BangDiem();
                temp.mssv = item.mssv;
                temp.tensv = item.hoten;
                temp.diemgiuaky = (double)item.diemgiuaky;
                temp.diemcuoiky = (double)item.diemcuoiky;
                temp.diemtrungbinh = (double)item.diemtrungbinh;
                result.Add(temp);                
            }
            if (result.Count > 0)
                return result;
            return null;
        }
        public static List<BangDiem2> GetStudentList_DependOn_StudentID(string idSearch)
        {
            List<BangDiem2> result = new List<BangDiem2>();
            var query = from sinhvien in db.SinhViens
                        join dangkyhoc in db.DangKyHocs on sinhvien.mssv equals dangkyhoc.mssv
                        join diem in db.Diems on dangkyhoc.madkh equals diem.madangkyhoc
                        join lop_monhoc in db.Lop_MonHocs on dangkyhoc.mamonhoc equals lop_monhoc.id
                        join monhoc in db.MonHocs on lop_monhoc.masomonhoc equals monhoc.mamonhoc
                        where sinhvien.mssv.Contains(idSearch)
                        select new { sinhvien.mssv, sinhvien.hoten, monhoc.mamonhoc, monhoc.tenmonhoc, diem.diemgiuaky, diem.diemcuoiky, diem.diemtrungbinh };
            foreach (var item in query)
            {
                BangDiem2 temp = new BangDiem2();
                temp.mssv = item.mssv;
                temp.tensv = item.hoten;
                temp.mamonhoc = item.mamonhoc;
                temp.tenmonhoc = item.tenmonhoc;
                temp.diemgiuaky = (double)item.diemgiuaky;
                temp.diemcuoiky = (double)item.diemcuoiky;
                temp.diemtrungbinh = (double)item.diemtrungbinh;
                result.Add(temp);
            }
            if (result.Count > 0)
                return result;
            return null;
        }
        public static List<BangDiem2> LayBangDiemTheoMSSV(String mssv)
        {
            List<BangDiem2> result = new List<BangDiem2>();
            var query = from sinhvien in db.SinhViens                       
                        join dangkyhoc in db.DangKyHocs on sinhvien.mssv equals dangkyhoc.mssv                        
                        join diem in db.Diems on dangkyhoc.madkh equals diem.madangkyhoc
                        join lop_monhoc in db.Lop_MonHocs on dangkyhoc.mamonhoc equals lop_monhoc.id
                        join monhoc in db.MonHocs on lop_monhoc.masomonhoc equals monhoc.mamonhoc
                        where sinhvien.mssv == mssv                              
                        select new { sinhvien.mssv, sinhvien.hoten,monhoc.mamonhoc, monhoc.tenmonhoc, diem.diemgiuaky, diem.diemcuoiky, diem.diemtrungbinh };
            foreach (var item in query)
            {
                BangDiem2 temp = new BangDiem2();
                temp.mssv = item.mssv;
                temp.tensv = item.hoten;
                temp.mamonhoc = item.mamonhoc;
                temp.tenmonhoc = item.tenmonhoc;
                temp.diemgiuaky = (double)item.diemgiuaky;
                temp.diemcuoiky = (double)item.diemcuoiky;
                temp.diemtrungbinh = (double)item.diemtrungbinh;
                result.Add(temp);
            }
            if (result.Count > 0)
                return result;
            return null;
        }
    }

    public class BangDiem
    {
        public String mssv{get;set;}
        public String tensv { get; set; }
        public double diemgiuaky { get; set; }
        public double diemcuoiky { get; set; }
        public double diemtrungbinh { get; set; }
    }

    public class BangDiem2
    {
        public String mssv { get; set; }
        public String tensv { get; set; }
        public String mamonhoc { get; set; }
        public String tenmonhoc { get; set; }
        public double diemgiuaky { get; set; }
        public double diemcuoiky { get; set; }
        public double diemtrungbinh { get; set; }
    }
}
